package me.sunsy.kit.test

import org.apache.poi.hssf.usermodel.HSSFWorkbook
import org.apache.poi.ss.usermodel.Cell
import org.apache.poi.ss.usermodel.CellType
import org.apache.poi.ss.usermodel.Row
import org.apache.poi.ss.usermodel.Sheet
import org.apache.poi.xssf.usermodel.XSSFSheet
import org.apache.poi.xssf.usermodel.XSSFWorkbook
import org.json.JSONArray
import org.json.JSONException
import org.json.JSONObject
import java.io.FileInputStream
import java.io.FileOutputStream
import java.io.IOException
import java.text.DecimalFormat
import java.util.regex.Pattern
import kotlin.collections.ArrayList
import kotlin.collections.HashMap

object Kit {

    fun judgeEmail(email: String): Boolean {
        val regEx1 = "^([a-z0-9A-Z]+[-|\\.]?)+[a-z0-9A-Z]@([a-z0-9A-Z]+(-[a-z0-9A-Z]+)?\\.)+[a-zA-Z]{2,}$"
        val p = Pattern.compile(regEx1)
        val m = p.matcher(email);
        return m.matches()
    }

    //<sheetIndex,line<rowIndex,rowValue>
    fun readExcel(filePath: String): List<List<Map<Int, String>>> {
        if (filePath.endsWith(".xlsx")) {
            return xssfExcel(filePath)
        } else {
            return hssfExcel(filePath)
        }
    }

    private fun xssfExcel(filePath: String): List<List<Map<Int, String>>> {
        var result = ArrayList<ArrayList<HashMap<Int, String>>>()
        var wb = XSSFWorkbook(FileInputStream(filePath))
        wb.sheetIterator().forEach { sheet ->
            result.add(readSheet(sheet))
        }
        return result
    }

    private fun hssfExcel(filePath: String): List<List<Map<Int, String>>> {
        var result = ArrayList<ArrayList<HashMap<Int, String>>>()
        var wb = HSSFWorkbook(FileInputStream(filePath))
        wb.sheetIterator().forEach { sheet ->
            result.add(readSheet(sheet))
        }
        return result
    }

    private fun readSheet(sheet: Sheet): ArrayList<HashMap<Int, String>> {
        //line<rowIndex,rowValue>
        var cs = ArrayList<HashMap<Int, String>>()
        var count = sheet.lastRowNum
        //循环每一行
        for (i in 0..count) {
            var rm = HashMap<Int, String>()
            var row = sheet.getRow(i)
            var rowNum = row.physicalNumberOfCells
            //循环每一列
            for (j in 0..rowNum) {
                rm.put(j, readStringCell(row.getCell(j)))
            }
            cs.add(rm);
        }
        return cs
    }

    private fun readStringCell(cell: Cell?): String {
        var result = ""
        if (cell == null) {
            return result
        }
        when (cell.cellType) {
            CellType.BLANK -> result = "null"
            CellType.BOOLEAN -> result = cell.booleanCellValue.toString()
            CellType.NUMERIC -> result = DecimalFormat("#.##").format(cell.numericCellValue)
            CellType.STRING -> result = cell.stringCellValue
            CellType.ERROR -> result = cell.errorCellValue.toString()
            CellType._NONE -> result = "null"
        }
        return result
    }

    /**
     * @param array array数组
     * @param sheetName sheet名称
     * @param outPath 输出地址
     */
    fun WriteExcel(array: JSONArray, sheetName: String?, outPath: String?) {
        try {
            val wb = XSSFWorkbook()
            val sheet = wb.createSheet(sheetName)
            val row: Row = sheet.createRow(0)
            val _0 = array.getJSONObject(0)
            val iterator: Iterator<*> = _0.keys()
            val list = initTitle(row, iterator)
            println(array.length())
            for (i in 0 until array.length()) {
                val item = array.getJSONObject(i)
                val itemRow: Row = sheet.createRow(i + 1)
                initValue(itemRow, list!!, item)
            }
            val out = FileOutputStream(outPath)
            wb.write(out)
            wb.close()
            out.close()
        } catch (e: IOException) {
            e.printStackTrace()
        } catch (e: JSONException) {
            e.printStackTrace()
        }
    }

    private fun initTitle(row: Row, iterator: Iterator<*>): List<String>? {
        var i = 0
        val list: MutableList<String> = java.util.ArrayList()
        while (iterator.hasNext()) {
            val key = iterator.next().toString()
            val cell = row.createCell(i++)
            cell.setCellValue(key)
            list.add(key)
        }
        return list
    }

    private fun initValue(row: Row, list: List<String>, obj: JSONObject) {
        var i = 0
        for (key in list) {
            try {
                val value = if (obj.has(key)) obj.get(key).toString() else ""
                row.createCell(i++).setCellValue(value)
            } catch (e: JSONException) {
                e.printStackTrace()
            }
        }
    }

}